﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯项目实施情况统计表通用操作类
    /// </summary>
    public class PotProjectServices: PotProjects
    {
        public Pager<List<PotProjectEntity>> GetPageList(PotProjectQuery query)
        {
            Pager<List<PotProjectEntity>> obj = new Pager<List<PotProjectEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotProject where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotProjectEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯项目实施情况统计表分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取县级填报数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public ResResult<MyReportModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<MyReportModel> obj = new ResResult<MyReportModel>();
            MyReportModel model = new MyReportModel();
            List<PotProjectEntity> list = new List<PotProjectEntity>();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select * from T_PotProject  where ReportId = @ReportId";
            if (reportService.CheckRepCreate("PotProject", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的项目实施情况统计表没有生成！";
            }
            else
            {
                ReportEntity reportModel = reportService.GetReportView("PotProject", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotProject"))
                    {
                        //表里没有数据，状态却为已填写,则将状态改为1未填写
                        if (model.ReportStatus.Equals("2"))
                        {
                            reportService.UpStatus(model.ReportId, "1", " ");
                            model.ReportStatus = "1";
                        }
                        //PotProjectEntity entity = new PotProjectEntity();
                        //entity.ReportId = model.ReportId;
                        //list.Add(entity);
                    }
                    else
                    {
                        list = dbCon.Query<PotProjectEntity>(strSql, new { ReportId = model.ReportId }).ToList();
                    }
                    model.list = list;

                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }
        public List<PotProjectEntity> ExcelToModel(string pathName, string fileSuffix, string ReportId)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<PotProjectEntity> list = new List<PotProjectEntity>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                int startRow = 4;
                IRow firstRow = sheet.GetRow(startRow);//从第5行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    PotProjectEntity model = new PotProjectEntity();
                    if (row.GetCell(0) != null)
                        model.ProjectName = row.GetCell(0).ToString();
                    if (row.GetCell(1) != null)
                        model.ProjectSource = row.GetCell(1).ToString();
                    if (row.GetCell(2) != null)
                        model.Age = row.GetCell(2).ToString();
                    if (row.GetCell(3) != null)
                    {
                        if (row.GetCell(3).CellType == CellType.Formula)
                        {
                            model.ProjectArrange = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 4);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(3).ToString()))
                            {
                                model.ProjectArrange = Convert.ToDecimal(row.GetCell(3).ToString());
                            }
                            else
                            {
                                model.ProjectArrange = 0;
                            }
                        }
                    }
                    else
                    {
                        model.ProjectArrange = 0;
                    }

                    if (row.GetCell(4) != null)
                    {
                        if (row.GetCell(4).CellType == CellType.Formula)
                        {
                            model.ProjectSupport = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 4);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(4).ToString()))
                            {
                                model.ProjectSupport = Convert.ToDecimal(row.GetCell(4).ToString());
                            }
                            else
                            {
                                model.ProjectSupport = 0;
                            }
                        }
                    }
                    else
                    {
                        model.ProjectSupport = 0;
                    }

                    if (row.GetCell(5) != null)
                    {
                        if (row.GetCell(5).CellType == CellType.Formula)
                        {
                            model.PlanArea = Decimal.Round(Convert.ToDecimal(row.GetCell(5).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(5).ToString()))
                            {
                                model.PlanArea = Convert.ToDecimal(row.GetCell(5).ToString());
                            }
                            else
                            {
                                model.PlanArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.PlanArea = 0;
                    }

                    if (row.GetCell(6) != null)
                    {
                        if (row.GetCell(6).CellType == CellType.Formula)
                        {
                            model.PlanYield = Decimal.Round(Convert.ToDecimal(row.GetCell(6).NumericCellValue), 1);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(6).ToString()))
                            {
                                model.PlanYield = Convert.ToDecimal(row.GetCell(6).ToString());
                            }
                            else
                            {
                                model.PlanYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.PlanYield = 0;
                    }
                    if (row.GetCell(7) != null)
                        model.PlanOther = row.GetCell(7).ToString();
                    if (row.GetCell(8) != null)
                        model.ImplePlace = row.GetCell(8).ToString();
                    if (row.GetCell(9) != null)
                        model.FVariety = row.GetCell(9).ToString();
                    if (row.GetCell(10) != null)
                    {
                        if (row.GetCell(10).CellType == CellType.Formula)
                        {
                            model.FArea = Decimal.Round(Convert.ToDecimal(row.GetCell(10).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(10).ToString()))
                            {
                                model.FArea = Convert.ToDecimal(row.GetCell(10).ToString());
                            }
                            else
                            {
                                model.FArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.FArea = 0;
                    }

                    if (row.GetCell(11) != null)
                    {
                        if (row.GetCell(11).CellType == CellType.Formula)
                        {
                            model.FYield = Decimal.Round(Convert.ToDecimal(row.GetCell(11).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(11).ToString()))
                            {
                                model.FYield = Convert.ToDecimal(row.GetCell(11).ToString());
                            }
                            else
                            {
                                model.FYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.FYield = 0;
                    }
                    ////////////////////////////////////////////
                    if (row.GetCell(12) != null)
                    {
                        if (row.GetCell(12).CellType == CellType.Formula)
                        {
                            model.CCArea = Decimal.Round(Convert.ToDecimal(row.GetCell(12).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(12).ToString()))
                            {
                                model.CCArea = Convert.ToDecimal(row.GetCell(12).ToString());
                            }
                            else
                            {
                                model.CCArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.CCArea = 0;
                    }

                    if (row.GetCell(13) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(13).ToString()))
                        {
                            model.CCSamples = Convert.ToInt32(row.GetCell(13).ToString());
                        }
                        else
                        {
                            model.CCSamples = 0;
                        }
                    }
                    else
                    {
                        model.CCSamples = 0;
                    }
                    if (row.GetCell(14) != null)
                        model.CCVariety = row.GetCell(14).ToString();
                    if (row.GetCell(15) != null)
                    {
                        if (row.GetCell(15).CellType == CellType.Formula)
                        {
                            model.CCavgMuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(15).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(15).ToString()))
                            {
                                model.CCavgMuYield = Convert.ToDecimal(row.GetCell(15).ToString());
                            }
                            else
                            {
                                model.CCavgMuYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.CCavgMuYield = 0;
                    }

                    if (row.GetCell(16) != null)
                    {
                        if (row.GetCell(16).CellType == CellType.Formula)
                        {
                            model.CCbestMuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(16).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(16).ToString()))
                            {
                                model.CCbestMuYield = Convert.ToDecimal(row.GetCell(16).ToString());
                            }
                            else
                            {
                                model.CCbestMuYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.CCbestMuYield = 0;
                    }
                    if (row.GetCell(17) != null)
                        model.CCdzVariety = row.GetCell(17).ToString();
                    ///////////////////////////////////////
                    if (row.GetCell(18) != null)
                    {
                        if (row.GetCell(18).CellType == CellType.Formula)
                        {
                            model.CCdzAvgMuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(18).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(18).ToString()))
                            {
                                model.CCdzAvgMuYield = Convert.ToDecimal(row.GetCell(18).ToString());
                            }
                            else
                            {
                                model.CCdzAvgMuYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.CCdzAvgMuYield = 0;
                    }
                    if (row.GetCell(19) != null)
                    {
                        if (row.GetCell(19).CellType == CellType.Formula)
                        {
                            model.AvgPrice = Decimal.Round(Convert.ToDecimal(row.GetCell(19).NumericCellValue), 1);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(19).ToString()))
                            {
                                model.AvgPrice = Convert.ToDecimal(row.GetCell(19).ToString());
                            }
                            else
                            {
                                model.AvgPrice = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AvgPrice = 0;
                    }

                    if (row.GetCell(20) != null)
                    {
                        if (row.GetCell(20).CellType == CellType.Formula)
                        {
                            model.AvgMuInvest = Decimal.Round(Convert.ToDecimal(row.GetCell(20).NumericCellValue), 1);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(20).ToString()))
                            {
                                model.AvgMuInvest = Convert.ToDecimal(row.GetCell(20).ToString());
                            }
                            else
                            {
                                model.AvgMuInvest = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AvgMuInvest = 0;
                    }

                    if (row.GetCell(21) != null)
                    {
                        if (row.GetCell(21).CellType == CellType.Formula)
                        {
                            model.AvgMuIncome = Decimal.Round(Convert.ToDecimal(row.GetCell(21).NumericCellValue), 1);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(21).ToString()))
                            {
                                model.AvgMuIncome = Convert.ToDecimal(row.GetCell(21).ToString());
                            }
                            else
                            {
                                model.AvgMuIncome = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AvgMuIncome = 0;
                    }

                    if (row.GetCell(22) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(22).ToString()))
                        {
                            model.Townships = Convert.ToInt32(row.GetCell(22).ToString());
                        }
                        else
                        {
                            model.Townships = 0;
                        }
                    }
                    else
                    {
                        model.Townships = 0;
                    }
                    if (row.GetCell(23) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(23).ToString()))
                        {
                            model.Villages = Convert.ToInt32(row.GetCell(23).ToString());
                        }
                        else
                        {
                            model.Villages = 0;
                        }
                    }
                    else
                    {
                        model.Villages = 0;
                    }
                    if (row.GetCell(24) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(24).ToString()))
                        {
                            model.Farrmers = Convert.ToInt32(row.GetCell(24).ToString());
                        }
                        else
                        {
                            model.Farrmers = 0;
                        }
                    }
                    else
                    {
                        model.Farrmers = 0;
                    }
                    if (row.GetCell(25) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(25).ToString()))
                        {
                            model.Peoples = Convert.ToInt32(row.GetCell(25).ToString());
                        }
                        else
                        {
                            model.Peoples = 0;
                        }
                    }
                    else
                    {
                        model.Peoples = 0;
                    }

                    if (row.GetCell(26) != null)
                    {
                        if (row.GetCell(26).CellType == CellType.Formula)
                        {
                            model.DDArea = Decimal.Round(Convert.ToDecimal(row.GetCell(26).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(26).ToString()))
                            {
                                model.DDArea = Convert.ToDecimal(row.GetCell(26).ToString());
                            }
                            else
                            {
                                model.DDArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.DDArea = 0;
                    }
                    if (row.GetCell(27) != null)
                        model.UnFinishDescribe = row.GetCell(27).ToString();
                    model.ReportId = ReportId;
                    list.Add(model);
                }
            }
            return list;
        }

        public ResResult<List<ProjectStatisModel>> getStatisListData(string AreaCode, string FillInYear, string ProjectName, string ProjectSource)
        {
            ResResult<List<ProjectStatisModel>> obj = new ResResult<List<ProjectStatisModel>>();
            List<ProjectStatisModel> list = new List<ProjectStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                list = getProvinStatisList(FillInYear, ProjectName, ProjectSource);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getCityStatisList(AreaCode, FillInYear,ProjectName,ProjectSource);
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear, ProjectName, ProjectSource);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public List<ProjectStatisModel> getProvinStatisList(string FillInYear, string ProjectName, string ProjectSource)
        {
            List<ProjectStatisModel> list = new List<ProjectStatisModel>();
            string strSql = "select t1.Name AreaName,t3.* from T_Area t1  join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code  join ";
            strSql += "T_PotProject t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProject'";
            strSql += "  and t2.FillInYear = @FillInYear ";
            strSql += "  and t1.ParentCode != '530000000000' and t1.Code != '530000000000' ";
            strSql += "  and t2.Status = '4' ";
            if (!string.IsNullOrEmpty(ProjectName))
            {
                strSql += "  and t3.ProjectName like '%" + ProjectName + "%'";
            }
            if (!string.IsNullOrEmpty(ProjectSource))
            {
                strSql += " and t3.ProjectSource ='" + ProjectSource + "'";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProjectStatisModel>(strSql, new {FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯项目信息失败", ex);
            }
            return list;
        }

        public List<ProjectStatisModel> getCityStatisList(string AreaCode, string FillInYear, string ProjectName, string ProjectSource)
        {
            List<ProjectStatisModel> list = new List<ProjectStatisModel>();
            string strSql = "select t1.Name AreaName,t3.* from T_Area t1  join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code  join ";
            strSql += "T_PotProject t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProject'";
            strSql += "  and t2.FillInYear = @FillInYear ";
            strSql += " and t1.ParentCode = @AreaCode";
            strSql += "  and t2.Status = '4' ";
            if (!string.IsNullOrEmpty(ProjectName))
            {
                strSql += "  and t3.ProjectName like '%" + ProjectName + "%'";
            }
            if (!string.IsNullOrEmpty(ProjectSource))
            {
                strSql += " and t3.ProjectSource ='" + ProjectSource + "'";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProjectStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯项目信息失败", ex);
            }
            return list;
        }

        public List<ProjectStatisModel> getCountyStatisList(string AreaCode, string FillInYear, string ProjectName, string ProjectSource)
        {
            List<ProjectStatisModel> list = new List<ProjectStatisModel>();
            string strSql = "select t1.Name AreaName,t3.* from T_Area t1  join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code  join ";
            strSql += "T_PotProject t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProject'";
            strSql += "  and t2.FillInYear = @FillInYear ";
            strSql += " and t1.Code = @AreaCode";
            strSql += "  and t2.Status = '4' ";
            if (!string.IsNullOrEmpty(ProjectName)){
                strSql += "  and t3.ProjectName like '%" + ProjectName + "%'";
            }
            if (!string.IsNullOrEmpty(ProjectSource))
            {
                strSql += " and t3.ProjectSource ='" + ProjectSource + "'";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProjectStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯项目信息失败", ex);
            }
            return list;
        }



    }
}